package com.example.wszb;

import android.util.Log;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
    数据库：wszb
    登陆表：login_user


 */

public class DBHelp {
    private static String driver = "com.mysql.jdbc.Driver";// MySql驱动
    //private static String url = "jdbc:mysql://192.168.76.128:3306/wszb?serverTimezone=GMT&useSSL=false";
    private static String url = "jdbc:mysql://47.101.173.130:3306/wszb?serverTimezone=GMT&useSSL=false";
    private static String user = "root";// 用户名
    private static String password = "WSZBnb666";// 密码





    private static Connection toConnect(){     //连接数据库
        Connection connection = null;
        try{
            Class.forName(driver);// 动态加载类
            // 尝试建立到给定数据库URL的连接
            connection = DriverManager.getConnection(url , user, password);
        }catch (Exception e){
            e.printStackTrace();
        }
        return connection;
    }

    public void toClose(Connection connection){    //关闭数据库连接
        if(connection != null){  // 关闭连接对象
            try{
                connection.close() ;
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }

    public boolean toLoginAccount(DataLogin x){   //登录账号
        //利用sql语句根据x.getAccount()查询对应密码并返回
        Connection conn = toConnect();
        String password = null;
        try{
            String sql = "select password from login_user where account = " + x.getAccount();
            PreparedStatement prestmt = conn.prepareStatement(sql);
            //prestmt.setString(1, x.getAccount());
            ResultSet rs = prestmt.executeQuery(sql);
            if (rs.next() ) {
                password = rs.getString("password");
                return password.equals(x.getPassword());
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return false;
    }


    public boolean toRegAccount(DataLogin x){  //注册该账号
        Connection conn = toConnect();
        try{
            String sql1 = "select account from login_user where account = " + x.getAccount();  //先查询账号是否存在，存在则返回false
            PreparedStatement prestmt1 = conn.prepareStatement(sql1);
            ResultSet rs = prestmt1.executeQuery(sql1);
            if ( rs.next() ) { //判断账号是否存在
                return false;
            }
            String sql2 = "insert into login_user(account,password) value ( ?, ?)";
            PreparedStatement prestmt2 = conn.prepareStatement(sql2);
            prestmt2.setString(1, x.getAccount());
            prestmt2.setString(2, x.getPassword());
            int t= prestmt2.executeUpdate();
            return t == 1;
        }catch(Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public boolean toReplacePassword(DataLogin x){  //重置密码，默认为123456
        Connection conn = toConnect();
        try{
            String sql1 = "select account from login_user where account = " + x.getAccount();  //先查询账号是否存在，存在则返回false
            PreparedStatement prestmt1 = conn.prepareStatement(sql1);
            ResultSet rs = prestmt1.executeQuery(sql1);
            if ( !rs.next() ) { //判断账号是否存在,不存在返回false
                return false;
            }
            String sql2 = "update login_user set password = 123456 where account = ?";
            PreparedStatement prestmt2 = conn.prepareStatement(sql2);
            prestmt2.setString(1, x.getAccount());
            int t= prestmt2.executeUpdate();
            return t == 1;
        }catch(Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public int getUserId(DataLogin x){
        Connection conn = toConnect();
        String password = null;
        try{
            String sql = "select password from login_user where account = " + x.getAccount();
            PreparedStatement prestmt = conn.prepareStatement(sql);
            //prestmt.setString(1, x.getAccount());
            ResultSet rs = prestmt.executeQuery(sql);
            if (rs.next() && x.getPassword().equals(rs.getString("password"))) {
                return rs.getInt("userid");
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return -1;
    }

}
